﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TpBaseObjects.Objects;
using System.Data;
using System.Data.OleDb;

namespace TpData.Layer {
    internal class OrganisationDataLayer : BaseAccessDataLayer {

        public virtual Organisation LoadOrganisation(string name, string suchbegriff) {
            string sql = "SELECT org.*, p.Plz, p.Ort, rf.RechtsFBez FROM (Organisation as org INNER JOIN Postleitzahl as p ON p.EPLZ = org.EPLZ) LEFT JOIN Rechtsform rf ON org.RechtsFKey = rf.RechtsFKey WHERE org.[Name] = @Name AND org.Suchbegriff = @Suchbegriff ORDER BY org.[Name]";
            using (IDataReader reader = this.GetDataReader(sql, new OleDbParameter("@Name", name), new OleDbParameter("@Suchbegriff", suchbegriff))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        public virtual Organisation LoadOrganisation(string name, int plz) {
            string sql = "SELECT top 1 org.*, p.Plz, p.Ort, rf.RechtsFBez FROM (Organisation as org INNER JOIN Postleitzahl as p ON p.EPLZ = org.EPLZ) LEFT JOIN Rechtsform rf ON org.RechtsFKey = rf.RechtsFKey WHERE org.[Name] = @Name AND p.PLz = @Plz ORDER BY org.[Name]";
            using (IDataReader reader = this.GetDataReader(sql, new OleDbParameter("@Name", name), new OleDbParameter("@Plz", plz.ToString()))) {
                return (reader.Read() ? LoadOrganisationFromDataReader(reader) : null);
            }
        }

        protected virtual Organisation LoadOrganisationFromDataReader(IDataReader reader) {
            Organisation org = new Organisation();
            org.OrgKey = this.GetInt32(reader, "OrgKey");
            org.OrgTS = this.GetString(reader, "OrgTS");
            org.Änderung = this.GetDateTime(reader, "Änderung");
            org.OrgName = this.GetString(reader, "Name");
            org.ZusätzeFW = this.GetString(reader, "ZusätzeFW");
            org.RechtsForm = this.GetString(reader, "RechtsFBez");
            org.Suchbegriff = this.GetString(reader, "Suchbegriff");
            org.Abteilung = this.GetString(reader, "Abteilung");
            org.Adresse = this.GetString(reader, "Adresse");
            org.EPLZ = this.GetInt32(reader, "EPLZ");
            org.Straße = this.GetString(reader, "Straße");
            org.TelNr1 = this.GetString(reader, "TelNr1");
            org.TelNr2 = this.GetString(reader, "TelNr2");
            org.FaxNr = this.GetString(reader, "FaxNr");
            org.EMail = this.GetString(reader, "e_Mail");
            org.Kurzwahlspeicher = this.GetString(reader, "Kurzwahlspeicher");
            org.KG = this.GetBoolean(reader, "KG");
            org.MB = this.GetBoolean(reader, "MB");
            org.TochterVon = this.GetInt32(reader, "TochterVon");
            org.Kreditlimit = this.GetInt32(reader, "Kreditlimit");
            org.Notiz = this.GetString(reader, "Notiz");
            org.Export = this.GetBoolean(reader, "export");
            org.Plz = this.GetString(reader, "Plz").ToString();
            org.Ort = this.GetString(reader, "Ort");
            org.Prefix = this.GetString(reader, "Prefix");

            FillMyPersonList(org);

            return org;
        }

        public virtual void FillMyPersonList(Organisation org) {

            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT Person.PersKey, Person.PersTS, Person.Änderung, PersOrg.Durchwahl, Person.Zuname, Person.Vorname, Person.Geschlecht, Titel.TitelBez as Titel1, Titel_1.TitelBez as Titel2, Person.Adresse, Person.EPLZ, Person.Straße, Vorwahl.Vorwahl, Person.Mobil,  ");
            sql.Append("Person.PrivTelefon, Person.PrivFax, Person.MailingSperre, Person.Notiz, Person.Mitarbeiter, Person.Kürzel, Person.eMail, Postleitzahl.PLZ, Postleitzahl.Ort, PersOrg.Fax, PersOrg.Tätigkeit as Job, Funktion.FunktionsBez, Berufstitel.BerufstitelBez, PersOrg.OrgKey ");
            sql.Append("FROM ((Vorwahl RIGHT JOIN (Titel RIGHT JOIN (Funktion RIGHT JOIN (Berufstitel RIGHT JOIN (Person LEFT JOIN PersOrg ON Person.PersKey = PersOrg.PersKey) ON Berufstitel.BerufstitelKey = PersOrg.BerufstitelKey) ON Funktion.FunktionsKey = ");
            sql.Append("PersOrg.FunktionsKey) ON Titel.TitelKey = Person.TitelKey1) ON Vorwahl.VorwahlKey = Person.VorwahlKey) LEFT JOIN Postleitzahl ON Person.EPLZ = Postleitzahl.EPLZ) LEFT JOIN Titel AS Titel_1 ON Person.TitelKey2 = Titel_1.TitelKey ");
            sql.Append("WHERE (((PersOrg.OrgKey)=@OrgKey))");
            sql.Append("ORDER BY Person.Zuname;");

            using (IDataReader reader = this.GetDataReader(sql.ToString(), new OleDbParameter("@OrgKey", org.OrgKey))) {
                while (reader.Read()) {
                    Person pers = new Person();
                    pers.Adresse = this.GetString(reader, "Adresse");
                    pers.Änderung = this.GetString(reader, "Änderung");
                    pers.Berufstitel = this.GetString(reader, "BerufstitelBez");
                    pers.Durchwahl = this.GetString(reader, "Durchwahl");
                    pers.eMail = this.GetString(reader, "eMail");
                    pers.EPLZ = this.GetInt32(reader, "EPLZ");
                    pers.Fax = this.GetString(reader, "Fax");
                    pers.Funktion = this.GetString(reader, "FunktionsBez");
                    pers.Geschlecht = this.GetShort(reader, "Geschlecht");
                    pers.Kürzel = this.GetString(reader, "Kürzel");
                    pers.MailingSperre = this.GetBoolean(reader, "MailingSperre");
                    pers.Mitarbeiter = this.GetBoolean(reader, "Mitarbeiter");
                    pers.Mobil = this.GetString(reader, "Mobil");
                    pers.Notiz = this.GetString(reader, "Notiz");
                    pers.OrgKey = this.GetInt32(reader, "OrgKey");
                    pers.Ort = this.GetString(reader, "Ort");
                    pers.PersKey = this.GetInt32(reader, "PersKey");
                    pers.PersTS = this.GetString(reader, "PersTS");
                    pers.Plz = this.GetString(reader, "PLZ");
                    pers.PrivFax = this.GetString(reader, "PrivFax");
                    pers.PrivTelefon = this.GetString(reader, "PrivTelefon");
                    pers.Straße = this.GetString(reader, "Straße");
                    pers.Tätigkeit = this.GetString(reader, "Job");
                    pers.Titel1 = this.GetString(reader, "Titel1");
                    pers.Titel2 = this.GetString(reader, "Titel2");
                    pers.Vorname = this.GetString(reader, "Vorname");
                    pers.Vorwahl = this.GetString(reader, "Vorwahl");
                    pers.Zuname = this.GetString(reader, "Zuname");

                    org.AddPerson(pers);

                }
            }
        }
    }
}
